package com.weavernorth.retailExperienceAward.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.weavernorth.retailExperienceAward.utils.sendDataToSapUtils;
import com.weavernorth.retailExperienceAward.utils.commonsUtil;
import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import com.weavernorth.util.dateUtils;
import org.apache.log4j.Logger;
import weaver.conn.ConnectionPool;
import weaver.conn.RecordSet;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.Util;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Date;

/**
 * @Author: bruseewang
 * @Package: com.weavernorth.action.zddptyjToSAP.service
 * @date: 2020/6/5 17:22
 * @description: 终端店铺分数统计推送至SAP(店长and店员)
 */
public class StoreRetailExpToSAP_dzService {
    public Integer cycle;
    public Logger log;

    public Integer getCycle() {
        return cycle;
    }

    public void setCycle(Integer cycle) {
        this.cycle = cycle;
    }

    public Logger getLog() {
        return log;
    }

    public void setLog(Logger log) {
        this.log = log;
    }

    public static ConnectionPool pool = ConnectionPool.getInstance();
    public Connection conn;
    public Statement statement;

    public void exeSendData() {
        RecordSet rs_getData = new RecordSet();
        RecordSet rs_kk = new RecordSet();
        String strKhzq = dateUtils.previousMonth(0);//0:当前年月所对应的周期
        if (cycle!=null) {
            strKhzq = dateUtils.previousMonth(cycle);
        }
        log.info("考核周期：" + strKhzq);
        Boolean isNext = true;//是否进行下一步操作
        try {
            //region[先将uf_zddpgrjjtj个人统计表中所有推送到SAP失败的数据进行更新]
            String strUpdata = "merge into uf_zddpgrjjtj a  using (select * from uf_zddpfstj) b " +
                    "on (b.FGS=a.FGS AND b.KHZQ=a.khzq and b.KHZQ='" + strKhzq + "' AND b.dp=a.dpmc AND a.tsjg=1) " +
                    "when matched then  " +
                    "  update  set a.khrq = b.khrq,a.zdf=b.zdf,a.fwbz40=b.fwbz40,a.clbz20=b.clbz20,a.crm5=b.crm5,a.yybz35=b.yybz35,a.hr10=b.hr10 ";
            rs_getData.executeQuery(strUpdata);
            //endregion
        } catch (Exception ex) {
            isNext = false;
            log.info("更新数据失败,异常消息：" + ex.toString());
        }
        //region[区域经理零售体验奖--->SAP]
        if (isNext) {
            //终端店铺个人奖金统计表：SELECT * FROM uf_zddpgrjjtj
            log.info("区域经理零售体验奖传SAP数据开始。。。");
            //1.获取终端店铺分数统计表中所有未取出到新表的数据
            String ids = "";
            String strGetIds = "SELECT id FROM uf_zddpfstj WHERE khzq='" + strKhzq + "'  and (sfyqs=0 OR  SFYQS is NULL) AND (zdf is not NULL  and (dpzt!=2 AND fwbz40 is not null AND clbz20 is not null AND crm5 is not NULL AND yybz35 is not null) or (dpzt=2))";
            log.info("终端店铺所有待取数据sql:" + strGetIds);
            rs_getData.executeQuery(strGetIds);
            while (rs_getData.next()) {
                ids += rs_getData.getString("id") + ",";
            }
            if (ids.length() > 0) {
                log.info("ids=" + ids);
                executeMethod(log, ids, strKhzq);
                try{
                    //region[财务扣款逻辑]
                    conn = pool.getConnection();
                    statement = conn.createStatement();
                    //uf_cwkkbb:不取BE和童装的数据 即ywdy not in(3,4)
                    String strQuery_kk = "SELECT a.khzq,a.yggh,a.sfqbkc,a.csjlsftykh,a.kkje,a.sfyqs,b.jjje FROM uf_cwkkbb a,uf_zddpgrjjtj b WHERE a.ywdy not in(3,4) AND (a.sfyqs is NULL OR  a.sfyqs=0) and a.khzq=b.khzq AND a.yggh=b.gh and a.khzq='" + strKhzq + "'";
                    log.info("零售体验奖及财务扣款联合查询：" + strQuery_kk);
                    rs_kk.execute(strQuery_kk);
                    String yggh = "";//员工工号
                    String sfqbkc = "";//是否全部扣除(0:是,1：否)
                    String csjlsftykh = "";//	城市经理是否同意考核(0:是,1：否)
                    String kkje = "";//扣款金额
                    String jjje = "";//奖金金额
                    String zzje = "";//最终金额
                    String kkqje = "";//财务扣款前金额
                    Integer temp;
                    while (rs_kk.next()) {
                        yggh = Util.null2String(rs_kk.getString("yggh"));
                        sfqbkc = Util.null2String(rs_kk.getString("sfqbkc"));
                        csjlsftykh = Util.null2String(rs_kk.getString("csjlsftykh"));
                        jjje = Util.null2s(rs_kk.getString("jjje"), "0.00");
                        kkqje=jjje;
                        BigDecimal b_jjje = new BigDecimal(jjje);
                        if ("0".equals(csjlsftykh)) {
                            //城市经理同意考核时,参与财务奖金扣除逻辑
                            if ("0".equals(sfqbkc)) {
                                //全部扣除时，奖金为0
                                zzje = "0.00";
                                kkje=jjje;
                            } else {
                                kkje = Util.null2s(rs_kk.getString("kkje"), "0.00");
                                BigDecimal b_kkje = new BigDecimal(kkje);
                                temp = b_kkje.compareTo(b_jjje);
                                if (temp == 0) {
                                    zzje = kkje;
                                } else if (temp > 0) {
                                    zzje = "0.00";
                                } else {
                                    zzje = b_jjje.subtract(b_kkje).toString();
                                }
                            }
                            String updateSql = "update uf_zddpgrjjtj set cwkkqjjje='"+kkqje+"',jjje='" + zzje + "',cwkkje='" + kkje + "'  where khzq='" + strKhzq + "' and gh='" + yggh + "'";
                            statement.addBatch(updateSql);
                            //uf_cwkkbb表更新是否已取数字段
                            String updateBack="update uf_cwkkbb set sfyqs='1' where khzq='" + strKhzq + "' and yggh='" + yggh + "'";
                            statement.addBatch(updateBack);
                            log.info("财务扣款后数据：员工编号：" + yggh + ",是否全部扣除：" + sfqbkc + ",城市经理是否同意考核：" + csjlsftykh + ",奖金金额：" + jjje + ",财务扣款：" + kkje + ",最终奖金：" + zzje);
                        }
                    }
                    Long begin1 = new Date().getTime();
                    statement.executeBatch();
                    Long end1 = new Date().getTime();
                    log.info("批量更新耗时：" + (end1 - begin1) + "(毫秒)");
                    conn.close();
                    statement.close();

                    //endregion
                }catch (Exception ex){
                    isNext = false;
                    log.info("更新数据失败,异常消息：" + ex.toString());
                }
            }
            if(isNext){
                sendDataToSap(log);
            }
        }
        //endregion
    }

    public void sendDataToSap(Logger log) {
        //报文请求体
        StringBuffer requestXml;
        RecordSet rs = new RecordSet();
        RecordSet rs_select = new RecordSet();
        RecordSet rsUpdate = new RecordSet();
        //获取个人奖金表所有待推送到SAP的数据
        String strSql = "SELECT * FROM uf_zddpgrjjtj WHERE tsjg IS NULL OR tsjg=1 and jjje is not null";
        log.info("查询终端店铺统计建模表的sql:" + strSql);
        rs.executeQuery(strSql);
        requestXml = new StringBuffer();
        //拼接请求体
        requestXml.append("<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:urn='urn:sap-com:document:sap:rfc:functions'>" +
                "<soapenv:Header/>" + "<soapenv:Body>" + "<urn:ZHRI_075>" + " <RETURN>" +
                "<!--Zero or more repetitions:-->" +
                "<item>" +
                "<TYPE></TYPE>" +
                "<ID></ID>" +
                "<NUMBER></NUMBER>" +
                "<MESSAGE></MESSAGE>" +
                "<LOG_NO></LOG_NO>" +
                "<LOG_MSG_NO></LOG_MSG_NO>" +
                "<MESSAGE_V1></MESSAGE_V1>" +
                "<MESSAGE_V2></MESSAGE_V2>" +
                "<MESSAGE_V3></MESSAGE_V3>" +
                "<MESSAGE_V4></MESSAGE_V4>" +
                "<PARAMETER></PARAMETER>" +
                "<ROW></ROW>" +
                "<FIELD></FIELD>" +
                "<SYSTEM></SYSTEM>" +
                "</item>" +
                "</RETURN>" +
                "<T_9133>" +
                "<!--Zero or more repetitions:-->");
        while (rs.next()) {
            //获取员工编号
            String strYgbh = Util.null2String(rs.getString("gh"));
            log.info("员工编号：" + strYgbh);
            //拼接XML
            requestXml.append(" <item>" + "<PERNR>" + strYgbh + "</PERNR>");

            //获取CRM评分
            String strCRM = Util.null2String(rs.getString("crm5"));
            log.info("CRM评分：" + strCRM);
            //拼接XML
            requestXml.append("<ZCRMPF>" + strCRM + "</ZCRMPF>");

            //获取零售运营评分
            String strLsyy = Util.null2String(rs.getString("yybz35"));
            log.info("零售运营评分：" + strLsyy);
            //拼接XML
            requestXml.append("<ZLSYYPF>" + strLsyy + "</ZLSYYPF>");

            //获取服务标准评分
            String strFwbz = Util.null2String(rs.getString("fwbz40"));
            log.info("服务标准评分：" + strFwbz);
            //拼接XML
            requestXml.append("<ZFWBZPF>" + strFwbz + "</ZFWBZPF>");

            //获取陈列管理
            String strClgl = Util.null2String(rs.getString("clbz20"));
            log.info("陈列管理评分：" + strClgl);
            //拼接XML
            requestXml.append("<ZCLGLPF>" + strClgl + "</ZCLGLPF>");

            //获取HR评分
            String strHR = Util.null2String(rs.getString("hr10"));
            log.info("获取HR评分：" + strHR);
            //拼接XML
            requestXml.append("<ZHRPF>" + strHR + "</ZHRPF>");

            //总得分
            String strZdf = Util.null2String(rs.getString("zdf"));
            log.info("总得分：" + strZdf);
            //拼接XML
            requestXml.append("<ZDF>" + strZdf + "</ZDF>");

            //评分日期
            String strPfrq = Util.null2String(rs.getString("khrq"));
            log.info("评分日期：" + strPfrq);
            int year = Integer.parseInt(strPfrq.substring(0, 4));
            int month = Integer.parseInt(strPfrq.substring(5, 7));
            log.info("传至SAP日期：" + dateUtils.getLastDayOfMonth(year, month));
            //姓名
            String strXm = Util.null2String(rs.getString("xm"));
            String strGzfw = sendDataToSapUtils.getGzfwFromUser(strXm);

            String strLockMess = sendDataToSapUtils.judgeLock(log,strGzfw, strPfrq);
            log.info("工资核算范围返回信息:" + strLockMess);
            if (!"".equals(strLockMess)) {
                strLockMess = "工号为:" + strYgbh + "," + strLockMess + "薪资核算结束后，工资范围解除锁定，流程可以继续提交";
            }

            //拼接XML
            requestXml.append("<ZSP_DATE>" + dateUtils.getLastDayOfMonth(year, month) + "</ZSP_DATE>");

            //奖金金额
            String strJjje = Util.null2String(rs.getString("jjje"));
            log.info("奖金金额：" + strJjje);
            //拼接XML
            requestXml.append("<ZJE>" + strJjje + "</ZJE>" + "</item>");
        }
        requestXml.append("</T_9133>" +
                "</urn:ZHRI_075>" +
                "</soapenv:Body>" +
                "</soapenv:Envelope>");
        log.info("requestXml:" + requestXml);
        JSONObject jsonResult = sendDataToSapUtils.toServices(log,requestXml.toString());
        log.info("最终返回值：" + jsonResult.toString());
        //返回的所有数据
        JSONArray jsonArray = jsonResult.getJSONArray("res");
        //根据顺序更新表
        rs_select.executeQuery(strSql);

        //计数,SAP多返回一次,则下标从1开始
        int intCount = 1;
        if (jsonArray.isEmpty() || jsonArray.size() < 1) {
            log.info("SAP接口返回信息为空！");
        } else {
            while (rs_select.next()) {
                //更新值
                String strUpdateVal = "1";
                //id
                String strId = Util.null2String(rs_select.getString("id"));
                String strFlag = jsonArray.getJSONObject(intCount).getString("flag");
                String strMess = jsonArray.getJSONObject(intCount).getString("mess");

                //返回成功则更新值为0,否则为失败,1
                if ("S".equals(strFlag)) {
                    strUpdateVal = "0";
                    strMess = "";
                }
                //更新：终端店铺个人奖金统计-店经理&销售顾问
                String strUpdateSql = "update uf_zddpgrjjtj set tsjg =?,sbyy=? where id=? ";
                log.info("更新sql:" + strUpdateSql + ",id:" + strId + ",结果:" + strUpdateVal + ",失败原因：" + strMess);
                rsUpdate.executeUpdate(strUpdateSql, strUpdateVal, strMess, strId);
                intCount++;
            }
        }
    }

    public void executeMethod(Logger log, String ids, String strKhzq) {
        RecordSet rs_insertData = new RecordSet();
        RecordSet rs_VO_jjjs = new RecordSet();
        RecordSet rs_sfyqs_update = new RecordSet();
        RecordSet rs_jjjs_update = new RecordSet();

        ids = ids.substring(0, ids.length() - 1);

        //要插入的数据,写入到日志中,便于查找问题
        String strGroupData = "SELECT a.fgs,a.zdf,a.dp as dpmc,a.dpzt,a.dpjb,a.pp,a.fwbz40,a.clbz20,a.crm5,a.yybz35,a.hr10,a.khrq,a.khzq,NVL( c.qjts, 0 ) AS qjts" +
                " FROM  uf_zddpfstj a INNER JOIN hrmresource b ON a.dp = b.departmentid and b.status not in (4,5,6,7) " +
                " LEFT JOIN w_all_leavelist c ON SUBSTR( a.khzq, 1, 4 ) = c.year AND SUBSTR( a.khzq, 5, 2 ) = c.month AND b.id = c.resourceid " +
                " WHERE a.id in (" + ids + ")";
        log.info("取出所有要抽取到新表的数据Sql：" + strGroupData);
        SyncUtil syncUtil = new SyncUtil();
        String strFormid = syncUtil.getModeIdBytableName("uf_zddpgrjjtj");
        String strInsertData = "INSERT INTO uf_zddpgrjjtj " +
                "(fgs,zdf, dpmc, dpzt, dpjb, pp, xm, gh, zw, fwbz40, clbz20, crm5,yybz35, hr10,khrq, khzq,formmodeid,modedatacreater,modedatacreatertype,modedatacreatedate,modedatacreatetime,qjts) " +
                "SELECT " +
                "a.fgs," +
                "a.zdf," +
                "a.dp AS dpmc," +
                "a.dpzt," +
                "a.dpjb," +
                "a.pp," +
                "b.ID AS xm," +
                "b.WORKCODE AS gh," +
                "b.jobtitle AS zw," +
                "a.fwbz40," +
                "a.clbz20," +
                "a.crm5," +
                "a.yybz35," +
                "a.hr10," +
                "a.khrq," +
                "a.khzq," +
                "" + strFormid + "," +
                "1," +
                "0," +
                "to_char(SYSDATE,'yyyy-mm-dd')," +
                "to_char(SYSDATE,'hh24:mi:ss')," +
                "NVL(c.qjts, 0 ) AS qjts" +
                " FROM  uf_zddpfstj a INNER JOIN hrmresource b ON a.dp = b.departmentid and b.status not in (4,5,6,7) " +
                " LEFT JOIN w_all_leavelist c ON SUBSTR( a.khzq, 1, 4 ) = c.year AND SUBSTR( a.khzq, 5, 2 ) = c.month AND b.id = c.resourceid " +
                " WHERE a.id in (" + ids + ") and a.zdf is not null";
        log.info("插入到个人奖金表Sql：" + strInsertData);
        Boolean insertResult = rs_insertData.executeUpdate(strInsertData);
        if (insertResult) {
            log.info("插入数据到个人奖金表成功！");
            //关联查询,得到要插入的人员的所对应的奖金基数
            String strSql_getJjjs = "SELECT a.id,a.fgs,a.zdf,a.xm,b.DEPARTMENTID,b.lastname, a.zw,b.jobtitle,c.jobtitlename, c.ecology_pinyin_search AS zwpy,d.jjjs as jjjs_gr," +
                    "a.qjts FROM uf_zddpgrjjtj a " +
                    "INNER JOIN hrmresource b ON a.xm=b.id INNER JOIN hrmjobtitles c ON a.zw=c.id LEFT JOIN uf_jjjspzb_gr d ON a.xm=d.ygxm " +
                    " left join uf_cwkkbb cwkk on cwkk.khzq=a.khzq and cwkk.yggh=a.gh and (cwkk.sfyqs IS NULL OR cwkk.sfyqs = 0 ) "+
                    "LEFT JOIN uf_jjjspzb f ON a.fgs=f.fgs WHERE a.khzq in(" + strKhzq + ")";
            log.info("获取人员对应的奖金基数sql:" + strSql_getJjjs);
            rs_VO_jjjs.executeQuery(strSql_getJjjs);
            String str_jjjs = "0.00";
            String str_jjjs_gr = "";
            String str_fgs = "";
            String str_dp = "";
            String str_zw = "";
            Double db_jjje = 0.00;
            String str_id = null;
            Double str_qjDays = 0.00;
            while (rs_VO_jjjs.next()) {
                //id
                str_id = Util.null2String(rs_VO_jjjs.getString("id"));
                //设置表权限
                ModeRightInfo modeRightInfoBanc = new ModeRightInfo();
                modeRightInfoBanc.setNewRight(true);
                modeRightInfoBanc.editModeDataShare(1, Integer.parseInt(strFormid), Util.getIntValue(str_id));
                //个人奖金基数
                str_jjjs_gr = Util.null2String(rs_VO_jjjs.getString("jjjs_gr"));
                //分公司
                str_fgs = Util.null2String(rs_VO_jjjs.getString("fgs"));
                //职务
                str_zw = Util.null2String(rs_VO_jjjs.getString("jobtitlename"));
                //部门(店铺)
                str_dp = Util.null2String(rs_VO_jjjs.getString("DEPARTMENTID"));
                String strZdf = rs_VO_jjjs.getString("zdf");
                if (strZdf == null || strZdf.equals("")) {
                    strZdf = updateZdf(log, rs_VO_jjjs.getString("zdf"), strKhzq, str_id, str_dp);
                }
                //总得分
                Double db_zdf = Util.getDoubleValue(strZdf, 0.00);
                //请假天数
                str_qjDays = Util.getDoubleValue(rs_VO_jjjs.getString("qjts"), 0.00);
                if ("".equals(str_jjjs_gr)) {
                    str_jjjs = getJjjsBydp(log, str_fgs, str_dp, str_zw);
                } else {
                    str_jjjs = str_jjjs_gr;
                }
                try {
                    db_jjje = (db_zdf / 100) * Util.getDoubleValue(str_jjjs, 0.00);
                    //根据请假天数调整奖金金额
                    db_jjje = commonsUtil.convertAmount(db_jjje, str_qjDays);
                    //计算奖金金额并更新
                    rs_jjjs_update.execute("update uf_zddpgrjjtj set jjje=" + db_jjje + " where id=" + str_id + "");

                } catch (Exception ex) {
                    ex.printStackTrace();
                    log.info("更新操作异常：" + ex.toString());
                }

            }
            //更新源表中取数是否成功的字段
            String is_S_Update = "update uf_zddpfstj set sfyqs=1 where id in (" + ids + ")";
            log.info("更新原表是否取数成功字段：" + is_S_Update);
            rs_sfyqs_update.executeQuery(is_S_Update);

        } else {
            log.info("插入数据到个人奖金表失败！");
        }

    }

    /***
     * 如果总得分为空时，重新计算
     * @param zdf
     * @param strKhzq
     * @param strId
     * @return
     */
    private String updateZdf(Logger log, String zdf, String strKhzq, String strId, String strDp) {
        String strResult = zdf;
        if (strResult == null || strResult.equals("")) {
            RecordSet rs_query = new RecordSet();
            String strGetZdf = "SELECT zdf FROM uf_zddpfstj where khzq='" + strKhzq + "' and dp='" + strDp + "'";
            log.info("查询总得分sql语句" + strGetZdf);
            rs_query.execute(strGetZdf);
            while (rs_query.next()) {
                strResult = rs_query.getString("zdf");
            }
            String str_update = "update uf_zddpgrjjtj set zdf=" + strResult + " where id=" + strId + "";
            log.info("更新总得分sql语句" + str_update);
            RecordSet rs_update = new RecordSet();
            try {
                rs_update.execute(str_update);
            } catch (Exception ex) {
                log.info("更新个人奖金表总的分出现异常，异常信息：" + ex.toString());
                strResult = zdf;
            }
        }
        return strResult;
    }

    /***
     * 根据分公司，部门(店铺)及职务查询分公司奖金基数表中对应的基数
     * @param strFgs
     * @param strDep
     * @param strDep
     * @return
     */
    public String getJjjsBydp(Logger log, String strFgs, String strDep, String strZw) {
        log.info("职务：" + strZw);
        RecordSet rs = new RecordSet();
        RecordSet rs_fgs = new RecordSet();
        String result = "0.00";
        String strdcjs = "";
        String strdyjs = "";
        String strmdzg = "";
        String strkgjs = "";
        String strsyyjs = "";
        int rows = 0;
        String strSql1 = "SELECT * FROM uf_jjjspzb where FGS=" + strFgs + " AND ";
        if (!"".equals(strDep)) {
            strSql1 += " dp=" + strDep + "";
            log.info("1.查询分公司奖金基数表Sql：" + strSql1);
            rs.executeQuery(strSql1);
            rows = rs.getCounts();
            if (rows > 0) {
                while (rs.next()) {
                    //店长基数
                    strdcjs = rs.getString(Util.null2String("dcjs"));
                    //销售顾问
                    strdyjs = rs.getString(Util.null2String("dyjs"));
                    //门店主管
                    strmdzg = rs.getString(Util.null2String("mdzg"));
                    //库管员
                    strkgjs = rs.getString(Util.null2String("kgjs"));
                    //收银员
                    strsyyjs = rs.getString(Util.null2String("syyjs"));
                    if (strZw.equals("店经理")) {
                        result = strdcjs;
                    } else if (strZw.equals("销售顾问")) {
                        result = strdyjs;
                    } else if (strZw.equals("门店主管")) {
                        result = strmdzg;
                    } else if (strZw.equals("库管员")) {
                        result = strkgjs;
                    } else if (strZw.equals("收银员")) {
                        result = strsyyjs;
                    }
                }
            } else {
                String strSql2 = "SELECT * FROM uf_jjjspzb where FGS=" + strFgs + " AND  dp is null or dp=''";
                log.info("2.查询分公司奖金基数表Sql：" + strSql2);
                rs_fgs.executeQuery(strSql2);
                rows = rs_fgs.getCounts();
                if (rows > 0) {
                    while (rs_fgs.next()) {
                        //店长基数
                        strdcjs = rs_fgs.getString(Util.null2String("dcjs"));
                        //销售顾问
                        strdyjs = rs_fgs.getString(Util.null2String("dyjs"));
                        //门店主管
                        strmdzg = rs_fgs.getString(Util.null2String("mdzg"));
                        //库管员
                        strkgjs = rs_fgs.getString(Util.null2String("kgjs"));
                        //收银员
                        strsyyjs = rs_fgs.getString(Util.null2String("syyjs"));
                        if (strZw.equals("店经理")) {
                            result = strdcjs;
                        } else if (strZw.equals("销售顾问")) {
                            result = strdyjs;
                        } else if (strZw.equals("门店主管")) {
                            result = strmdzg;
                        } else if (strZw.equals("库管员")) {
                            result = strkgjs;
                        } else if (strZw.equals("收银员")) {
                            result = strsyyjs;
                        }
                    }
                }

            }
        }
        if ("".equals(result)) {
            result = "0.00";
        }
        return result;
    }
}
